<?php

class StaffAdminController extends AppController
{
    # Controller Name to be used
    var $name = 'StaffAdmin';

    # Models to be used
    var $uses = array(
        'StaffUser', 'StudentUser', 'BankBranch', 'FeeType', 'AcademicSession', 'Program','Bank'
    );

    # set the layout to use
    var $layout = 'staff_layout';

    function __validateLoginStatus()
    {
        if ($this->params['action'] != 'login' && $this->params['action'] != 'logout') {
            if ($this->Session->check('loginData') == false) {
                $this->redirect(array('controller' => 'Staff', 'action' => 'login?ref=' . $this->params['action']));
            } else {
                $auth_user = $this->Session->read('loginData');
                //check if the user has not done multiple login
                $db_login_session = $this->getLoginSession($auth_user['id']);
                $login_session = $auth_user['login_session'];
                if ($db_login_session != $login_session) {
                    $this->Session->setFlash('Your session has expired!');
                    $this->redirect(array('action' => 'logout'));
                }
                //authorize the user, if the user is not authorized to view this page then redirect them to their session
                //$auth_user = $this->Session->read('loginData');
                if ($auth_user['user_type'] != 'staff_user') {
                    if ($auth_user['user_type'] == 'student_user') {
                        $this->redirect(array('controller' => 'Student', 'action' => 'index'));
                    } elseif ($auth_user['user_type'] == 'bank_user') {
                        $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
                    } else {
                        $this->redirect(array('action' => 'logout'));
                    }
                } else { // For Admin Staff only
                    $user_level = $auth_user['user_level'];
                    if ($user_level != 'Admin') {
                        $this->redirect(array('controller' => 'Staff', 'action' => 'index'));
                    }
                }
            }
        }
    }

    private function getLoginSession($user_id)
    {
        $data = $this->StaffUser->find('first', array(
            'conditions' => array('id' => $user_id),
            'recursive' => -1
        ));
        return $data['StaffUser']['login_session'];
    }

    function  beforeFilter()
    {
        parent::beforeFilter();
        $this->__validateLoginStatus();
    }

    public function logout()
    {
        $this->redirect(array('controller' => 'staff', 'action' => 'logout'));
    }


    # Controller actions
    /**
     * @name index
     * @param void
     * @return Array of data.
     * @access public
     */
    function index()
    {
        $this->redirect(array('action' => 'staff_users'));
    }


    function staff_users($type = 'get')
    {

        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;
            $auth_user = $this->Session->read('loginData');
            switch ($type) {
                case 'get' :
                    $_POST = $this->StaffUser->sanitize($_POST);
                    /**  Get posted data */
                    $page = isset($_POST['page']) ? $_POST['page'] : 1;
                    /** The current page */
                    $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
                    /** Sort column */
                    $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
                    /** Sort order */
                    $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';
                    /** Search string */
                    $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
                    $limit = $rp;
                    $start = ($page - 1) * $rp;

                    //get users id for this company only

                    $condition_array = array(
                        'StaffUser.deleted' => 'n'
                    );

                    if (!empty($search_query)) {
                        if ($qtype == 'username') {
                            /*$condition_array = array(
                                'User.username' => $search_query,
                                'User.deleted' => 'n'
                            );*/
                        } else {
                            /* $condition_array = array(
                                 "User.$qtype LIKE" => $search_query . '%',
                                 'User.deleted' => 'n'
                             );*/
                        }
                    }

                    $data_table = $this->StaffUser->find('all', array('conditions' => $condition_array, 'order' => "StaffUser.$sortname $sortorder", 'limit' => $start . ',' . $limit, 'recursive' => -1));
                    $data_table_count = $this->StaffUser->find('count', array('conditions' => $condition_array, 'recursive' => -1));

                    $total_records = $data_table_count;

                    if ($data_table) {
                        $return_arr = array();
                        foreach ($data_table as $obj) {
                            $return_arr[] = array(
                                'id' => $obj['StaffUser']['id'],
                                'cell' => array(
                                    $obj['StaffUser']['id'],
                                    $obj['StaffUser']['fname'],
                                    $obj['StaffUser']['mname'],
                                    $obj['StaffUser']['lname'],
                                    $obj['StaffUser']['username'],
                                    $obj['StaffUser']['user_level'],
                                )
                            );
                        }
                        return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
                    }
                    break;

                case 'save' :
                    if ($_POST['id'] == 0) {
                        //check if username does not exist for in this company
                        $username = $_POST['username'];
                        $res = $this->_validateUsername($username);
                        if ($res) {
                            return json_encode(array('success' => 1, 'msg' => 'Username already exist.'));
                        }
                    }
                    $data = array('StaffUser' => $_POST);
                    $pass = '';
                    if ($_POST['id'] == 0) {
                        $pass = $this->randomPassString(8);
                        $data['StaffUser']['password'] = md5($pass);
                        $data['StaffUser']['user_type'] = 'staff_user';
                    }

                    if ($_POST['id'] > 0) {
                        $data['StaffUser']['modified_by'] = $auth_user['id'];
                    } else {
                        $data['StaffUser']['created_by'] = $auth_user['id'];
                    }

                    if ($this->StaffUser->save($data['StaffUser'])) {
                        if ($_POST['id'] > 0) {
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved'));
                        } else { //If new pass back extra data if needed.
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved!  The default password is ' . $pass, 'id' => $this->StaffUser->id));
                        }
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Some errors occurred.'));
                    }
                    break;

                case 'reset_password':
                    $data = array(
                        'id' => $_POST['id'],
                        'password' => md5(trim($_POST['password']))
                    );
                    if ($this->StaffUser->save($data)) {
                        return json_encode(array('code' => 0, 'msg' => 'Password has been reset.'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Password could not be reset.'));
                    }

                    break;

                case 'delete':
                    $ids = $_POST['ids'];
                    $modObj = ClassRegistry::init('StaffUser');
                    $result = $modObj->updateAll(
                        array('StaffUser.deleted' => "'y'"),
                        array('StaffUser.id' => $ids)
                    );
                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Deleted!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be deleted'));
                    }
                    break;
            }
        }

        $user_levels = array(
            array('id' => 'Admin', 'name' => 'Admin'),
            array('id' => 'Accountant', 'name' => 'Accountant'),
            array('id' => 'Academic Registration Officer', 'name' => 'Academic Registration Officer'),
            array('id' => 'Academic Examination Officer', 'name' => 'Academic Examination Officer'),
            array('id' => 'Academic Student Services', 'name' => 'Academic Student Services')
        );


        $this->set(compact('user_levels'));
    }


    function _validateUsername($username = null, $type = 'staff')
    {
        if ($type == 'staff') {
            $data = $this->StaffUser->find('first', array(
                'conditions' => array('StaffUser.username' => $username),
                'recursive' => -1
            ));
        } else {
            $data = $this->StudentUser->find('first', array(
                'conditions' => array('StudentUser.username' => $username),
                'recursive' => -1
            ));
        }

        if ($data) {
            return true;
        } else {
            return false;
        }
    }


    function randomPassString($length)
    {
        return $this->randomString($length);
    }


    /* Students list */
    function student_users($type = 'get')
    {

        //$this->redirect(array('action' => 'index'));

        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;
            $auth_user = $this->Session->read('loginData');
            switch ($type) {
                case 'get' :
                    $_POST = $this->StaffUser->sanitize($_POST);
                    /**  Get posted data */
                    $page = isset($_POST['page']) ? $_POST['page'] : 1;
                    /** The current page */
                    $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
                    /** Sort column */
                    $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
                    /** Sort order */
                    $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';
                    /** Search string */
                    $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
                    $limit = $rp;
                    $start = ($page - 1) * $rp;

                    //get users id for this company only

                    $condition_array = array(
                        'StudentUser.deleted' => 'n',
                    );

                    if (!empty($search_query)) {
                        if ($qtype == 'index_number') {
                            $condition_array = array(
                                'StudentUser.index_number' => $search_query,
                                'StudentUser.deleted' => 'n',
                            );
                        }
                        if ($qtype == 'reg_no') {
                            $condition_array = array(
                                'StudentUser.reg_no' => $search_query,
                                'StudentUser.deleted' => 'n',
                            );
                        } else {
                            $condition_array = array(
                                "StudentUser.$qtype LIKE" => $search_query . '%',
                                'StudentUser.deleted' => 'n',
                            );
                        }
                    }

                    $contain = array(
                        'Program' => array('fields' => array('Program.id', 'Program.short_name', 'Program.progid'))
                    );

                    $data_table = $this->StudentUser->find('all', array('conditions' => $condition_array, 'contain' => $contain, 'order' => "StudentUser.$sortname $sortorder", 'limit' => $start . ',' . $limit, 'recursive' => 1));
                    $data_table_count = $this->StudentUser->find('count', array('conditions' => $condition_array, 'recursive' => -1));

                    $total_records = $data_table_count;

                    if ($data_table) {
                        $return_arr = array();
                        foreach ($data_table as $obj) {
                            $active = $obj['StudentUser']['active'];
                            $active_lable = 'Enabled';
                            if ($active == 'n') {
                                $active_lable = 'Disabled';
                            }
                            $return_arr[] = array(
                                'id' => $obj['StudentUser']['id'],
                                'cell' => array(
                                    $active_lable,
                                    $obj['StudentUser']['user_level'],
                                    $obj['StudentUser']['reg_no'],
                                    $obj['StudentUser']['index_number'],
                                    ucwords(strtolower($obj['StudentUser']['fname'])),
                                    ucwords(strtolower($obj['StudentUser']['mname'])),
                                    ucwords(strtolower($obj['StudentUser']['lname'])),
                                    $obj['StudentUser']['gender'],
                                    $obj['StudentUser']['date_of_birth'],
                                    $obj['StudentUser']['date_of_admission'],
                                    $obj['StudentUser']['date_of_completion'],
                                    $obj['StudentUser']['nationality'],
                                    $obj['StudentUser']['homephone'],
                                    $obj['StudentUser']['cellphone'],
                                    $obj['StudentUser']['email'],
                                    $obj['Program']['progid'],
                                    $obj['StudentUser']['level'],
                                    $obj['StudentUser']['entry_level'],
                                    $obj['StudentUser']['applicant_id'],
                                    $obj['StudentUser']['classification'],
                                    $obj['StudentUser']['graduate'] == 1 ? 'Yes' : 'No',
                                    $obj['StudentUser']['completed'] == 1 ? 'Yes' : 'No'
                                )
                            );
                        }
                        return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
                    }
                    break;

                case 'save' :
                    if ($_POST['id'] == 0) {
                        //check if username does not exist for in this company
                        $username = $_POST['reg_no'];
                        $res = $this->_validateUsername($username, 'student');
                        if ($res) {
                            return json_encode(array('success' => 1, 'msg' => 'Username already exist.'));
                        }
                    }
                    $data = array('StudentUser' => $_POST);
                    //$pass = '';
                    if ($_POST['id'] == 0) {
                        $pass = trim($_POST['date_of_birth']);
                        $data['StudentUser']['password'] = md5($pass);
                        $data['StudentUser']['username'] = $username;
                        $data['StaffUser']['created_by'] = $auth_user['id'];
                        $pro = $this->Program->find('first', array(
                            'fields' => array('id', 'progid'),
                            'conditions' => array('id' => $_POST['program_id']),
                            'recursive' => -1
                        ));
                        $data['StaffUser']['progid'] = $pro['Program']['progid'];
                    } else {
                        $data['StudentUser']['modified_by'] = $auth_user['id'];
                    }

                    if ($this->StudentUser->save($data['StudentUser'])) {
                        if ($_POST['id'] > 0) {
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved'));
                        } else { //If new pass back extra data if needed.
                            //return json_encode(array('code' => 0, 'msg' => 'Data Saved'));
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved!  The default password is the date of birth', 'id' => $this->StudentUser->id));
                        }
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Some errors occurred.'));
                    }
                    break;

                case 'reset_password':
                    $data = array(
                        'id' => $_POST['id'],
                        'password' => md5(trim($_POST['password']))
                    );
                    if ($this->StudentUser->save($data)) {
                        return json_encode(array('code' => 0, 'msg' => 'Password has been reset.'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Password could not be reset.'));
                    }

                    break;

                case 'delete':
                    $ids = $_POST['ids'];
                    $modObj = ClassRegistry::init('StudentUser');
                    $result = $modObj->updateAll(
                        array('StudentUser.deleted' => "'y'"),
                        array('StudentUser.id' => $ids)
                    );
                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Deleted!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be deleted'));
                    }
                    break;

                case 'profile_config':
                    $data = $_POST['profile_config_opt'];
                    $modObj = ClassRegistry::init('Config');
                    $result = $modObj->save(array(
                        'id' => 1,
                        'students_can_edit_other_profile' => $data
                    ));

                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Updated!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be Updated'));
                    }
                    break;
            }
        }

        $pro = $this->Program->find('all', array(
            'fields' => array('id', 'progid', 'short_name'),
            //'conditions'=>array('deleted'=>'n'),
            'recursive' => -1
        ));
        $programs = array();
        foreach ($pro as $p) {
            $programs[] = array(
                'id' => $p['Program']['id'],
                'name' => $p['Program']['progid']
            );
        }
        $classify = array();
        foreach ($this->classification as $c) {
            $classify[] = array(
                'id' => $c,
                'name' => $c
            );
        }

        $levels = array();
        foreach ($this->levels as $l) {
            $levels[] = array(
                'id' => $l,
                'name' => $l
            );
        }

        $countries = array();
        foreach ($this->countries as $l) {
            $countries[] = array(
                'id' => $l,
                'name' => $l
            );
        }

        $config = $this->config;
        $this->set(compact('programs', 'classify', 'levels', 'config', 'countries'));
    }


    function import_students()
    {
        $mesg = false;
        $this->layout = 'import_layout';
        if ($this->request->is('post')) {
            $upload_file = false;
            $save_raw = $save = $this->request->data;
            if (isset($save_raw['Import']['attach'])) {
                if (!empty($save_raw['Import']['attach']['name'])) {
                    $upload_file = true;
                }
            }
            $upload_success = false;
            if ($upload_file) {
                $upload_info = array(
                    'save_path' => 'files/import_students/',
                    'folder' => 'import_students/',
                    'file_name' => $save_raw['Import']['attach']['name'], //$_FILES['uploadfile']['name']
                    'file_type' => $save_raw['Import']['attach']['type'], //$_FILES['uploadfile']['type'];
                    'file_tmp_name' => $save_raw['Import']['attach']['tmp_name'], //$_FILES['uploadfile']['tmp_name']
                    'file_size' => $save_raw['Import']['attach']['size'], //$_FILES['uploadfile']['size']
                    'file_name_prefix' => time(),
                    'check_file_type' => array()
                );
                $result = $this->uploadFile($upload_info);
                if ($result['status']) {
                    $upload_success = true;
                } else {
                    $upload_success = false;
                }
            }

            if ($upload_success) {
                //debug($result);
                $result2 = $this->do_students_import($result['file_name'], $result['file_path']);
                $mesg = $result2['mesg'];
            }
        }
        $this->set(compact('mesg'));
    }


    function do_students_import($filename, $file_path)
    {
        ini_set("memory_limit", "512M");
        set_time_limit('1200');

        $auth_user = $this->Session->read('loginData');
        # folder path structure
        $folder = "files/";
        # setup directory pathname
        $folderAbsPath = WWW_ROOT . $folder;
        //do excel import
        $status = true;
        $sheet_name = 'Page_1';

        $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
        $cacheSettings = array(' memoryCacheSize ' => '8MB');
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        /**  Create a new Reader of the type defined in $inputFileType  **/
        $objReader = PHPExcel_IOFactory::createReader('CSV'); //Excel5 for Excel and CSV For csv
        /**  Advise the Reader of which WorkSheets we want to load  **/
        //$objReader->setLoadSheetsOnly($sheet_name); //To read CSV COMMENT OUT
        /** Advise the Reader that we only want to load cell data, not formatting **/
        //$objReader->setReadDataOnly(true);//To read CSV COMMENT OUT
        /**  Load $inputFileName to a PHPExcel Object  **/
        $objPHPExcel = $objReader->load($folderAbsPath . $file_path);
        /**  Advise the Reader of which WorkSheets we want to load  **/

        $worksheetTitle = $objPHPExcel->getActiveSheet()->getTitle();
        $highestRow = $objPHPExcel->getActiveSheet()->getHighestRow(); // e.g. 10
        $highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn(); // e.g 'F'

        if($highestRow > 25000){
            return array('status' => false, 'mesg' => 'Exceeded maximum records (25000 records) per upload. <br /> Total records to process can be up to 25000 records per upload. ');
        }

        $index_number = trim($objPHPExcel->getActiveSheet()->getCell("A1")->getValue());
        $fname = trim($objPHPExcel->getActiveSheet()->getCell("B1")->getValue());
        $lname = trim($objPHPExcel->getActiveSheet()->getCell("C1")->getValue());
        $mname = trim($objPHPExcel->getActiveSheet()->getCell("D1")->getValue());
        $stud_id = trim($objPHPExcel->getActiveSheet()->getCell("E1")->getValue());
        $reg_no = trim($objPHPExcel->getActiveSheet()->getCell("F1")->getValue());
        $gender = trim($objPHPExcel->getActiveSheet()->getCell("G1")->getValue());
        $title_id = trim($objPHPExcel->getActiveSheet()->getCell("H1")->getValue());
        $date_of_birth = trim($objPHPExcel->getActiveSheet()->getCell("I1")->getValue());
        $date_of_admission = trim($objPHPExcel->getActiveSheet()->getCell("J1")->getValue());
        $date_of_completion = trim($objPHPExcel->getActiveSheet()->getCell("K1")->getValue());
        $prog_id = trim($objPHPExcel->getActiveSheet()->getCell("L1")->getValue());
        $program_id = trim($objPHPExcel->getActiveSheet()->getCell("M1")->getValue());
        $major_id = trim($objPHPExcel->getActiveSheet()->getCell("N1")->getValue());
        $level = trim($objPHPExcel->getActiveSheet()->getCell("O1")->getValue());
        $entry_level = trim($objPHPExcel->getActiveSheet()->getCell("P1")->getValue());
        $hall_id = trim($objPHPExcel->getActiveSheet()->getCell("Q1")->getValue());
        $ssnit = trim($objPHPExcel->getActiveSheet()->getCell("R1")->getValue());
        $nationality = trim($objPHPExcel->getActiveSheet()->getCell("S1")->getValue());
        $homephone = trim($objPHPExcel->getActiveSheet()->getCell("T1")->getValue());
        $cellphone = trim($objPHPExcel->getActiveSheet()->getCell("U1")->getValue());
        $email = trim($objPHPExcel->getActiveSheet()->getCell("V1")->getValue());
        $applicant_id = trim($objPHPExcel->getActiveSheet()->getCell("W1")->getValue());
        $paytype = trim($objPHPExcel->getActiveSheet()->getCell("X1")->getValue());
        $completed = trim($objPHPExcel->getActiveSheet()->getCell("Y1")->getValue());
        $graduate = trim($objPHPExcel->getActiveSheet()->getCell("Z1")->getValue());
        $status = trim($objPHPExcel->getActiveSheet()->getCell("AA1")->getValue());
        $classification = trim($objPHPExcel->getActiveSheet()->getCell("AB1")->getValue());
        $activestatus = trim($objPHPExcel->getActiveSheet()->getCell("AC1")->getValue());
        $src_id = trim($objPHPExcel->getActiveSheet()->getCell("AD1")->getValue());
        $dn_status = trim($objPHPExcel->getActiveSheet()->getCell("AE1")->getValue());

        if ($index_number != 'index_number') {
            return array('status' => false, 'mesg' => '1-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($fname != 'fname') {
            return array('status' => false, 'mesg' => '2-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($lname != 'lname') {
            return array('status' => false, 'mesg' => '3-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($mname != 'mname') {
            return array('status' => false, 'mesg' => '4-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($stud_id != 'stud_id') {
            return array('status' => false, 'mesg' => '5-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($reg_no != 'reg_no') {
            return array('status' => false, 'mesg' => '6-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($gender != 'gender') {
            return array('status' => false, 'mesg' => '7-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($title_id != 'title_id') {
            return array('status' => false, 'mesg' => '8-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($date_of_birth != 'date_of_birth') {
            return array('status' => false, 'mesg' => '9-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($date_of_admission != 'date_of_admission') {
            return array('status' => false, 'mesg' => '10-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($date_of_completion != 'date_of_completion') {
            return array('status' => false, 'mesg' => '11-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($prog_id != 'prog_id') {
            return array('status' => false, 'mesg' => '12-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($program_id != 'program_id') {
            return array('status' => false, 'mesg' => '13-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($major_id != 'major_id') {
            return array('status' => false, 'mesg' => '14-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($level != 'level') {
            return array('status' => false, 'mesg' => '15-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($entry_level != 'entry_level') {
            return array('status' => false, 'mesg' => '16-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($hall_id != 'hall_id') {
            return array('status' => false, 'mesg' => '17-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($ssnit != 'ssnit') {
            return array('status' => false, 'mesg' => '18-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($nationality != 'nationality') {
            return array('status' => false, 'mesg' => '19-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($homephone != 'homephone') {
            return array('status' => false, 'mesg' => '20-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($cellphone != 'cellphone') {
            return array('status' => false, 'mesg' => '21-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($email != 'email') {
            return array('status' => false, 'mesg' => '22-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($applicant_id != 'applicant_id') {
            return array('status' => false, 'mesg' => '23-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($paytype != 'paytype') {
            return array('status' => false, 'mesg' => '24-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($completed != 'completed') {
            return array('status' => false, 'mesg' => '25-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($graduate != 'graduate') {
            return array('status' => false, 'mesg' => '26-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($status != 'status') {
            return array('status' => false, 'mesg' => '27-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($classification != 'classification') {
            return array('status' => false, 'mesg' => '28-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($activestatus != 'activestatus') {
            return array('status' => false, 'mesg' => '29-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($src_id != 'src_id') {
            return array('status' => false, 'mesg' => '30-Invalid template. Please make sure you are using the right template for the upload.');
        }
        if ($dn_status != 'dn_status') {
            return array('status' => false, 'mesg' => '31-Invalid template. Please make sure you are using the right template for the upload.');
        }

        $save = array();
        $current_academic_year = true;


        for ($row = 2; $row < ($highestRow + 1); ++$row) {
            $index_number = trim($objPHPExcel->getActiveSheet()->getCell("A" . $row)->getValue());
            $fname = trim($objPHPExcel->getActiveSheet()->getCell("B" . $row)->getValue());
            $lname = trim($objPHPExcel->getActiveSheet()->getCell("C" . $row)->getValue());
            $mname = trim($objPHPExcel->getActiveSheet()->getCell("D" . $row)->getValue());
            $stud_id = trim($objPHPExcel->getActiveSheet()->getCell("E" . $row)->getValue());
            $reg_no = trim($objPHPExcel->getActiveSheet()->getCell("F" . $row)->getValue());
            $gender = trim($objPHPExcel->getActiveSheet()->getCell("G" . $row)->getValue());
            $title_id = trim($objPHPExcel->getActiveSheet()->getCell("H" . $row)->getValue());
            $date_of_birth = trim($objPHPExcel->getActiveSheet()->getCell("I" . $row)->getValue());
            $date_of_admission = trim($objPHPExcel->getActiveSheet()->getCell("J" . $row)->getValue());
            $date_of_completion = trim($objPHPExcel->getActiveSheet()->getCell("K" . $row)->getValue());
            $prog_id = trim($objPHPExcel->getActiveSheet()->getCell("L" . $row)->getValue());
            $program_id = trim($objPHPExcel->getActiveSheet()->getCell("M" . $row)->getValue());
            $major_id = trim($objPHPExcel->getActiveSheet()->getCell("N" . $row)->getValue());
            $level = trim($objPHPExcel->getActiveSheet()->getCell("O" . $row)->getValue());
            $entry_level = trim($objPHPExcel->getActiveSheet()->getCell("P" . $row)->getValue());
            $hall_id = trim($objPHPExcel->getActiveSheet()->getCell("Q" . $row)->getValue());
            $ssnit = trim($objPHPExcel->getActiveSheet()->getCell("R" . $row)->getValue());
            $nationality = trim($objPHPExcel->getActiveSheet()->getCell("S" . $row)->getValue());
            $homephone = trim($objPHPExcel->getActiveSheet()->getCell("T" . $row)->getValue());
            $cellphone = trim($objPHPExcel->getActiveSheet()->getCell("U" . $row)->getValue());
            $email = trim($objPHPExcel->getActiveSheet()->getCell("V" . $row)->getValue());
            $applicant_id = trim($objPHPExcel->getActiveSheet()->getCell("W" . $row)->getValue());
            $paytype = trim($objPHPExcel->getActiveSheet()->getCell("X" . $row)->getValue());
            $completed = trim($objPHPExcel->getActiveSheet()->getCell("Y" . $row)->getValue());
            $graduate = trim($objPHPExcel->getActiveSheet()->getCell("Z" . $row)->getValue());
            $status = trim($objPHPExcel->getActiveSheet()->getCell("AA" . $row)->getValue());
            $classification = trim($objPHPExcel->getActiveSheet()->getCell("AB" . $row)->getValue());
            $activestatus = trim($objPHPExcel->getActiveSheet()->getCell("AC" . $row)->getValue());
            $src_id = trim($objPHPExcel->getActiveSheet()->getCell("AD" . $row)->getValue());
            $dn_status = trim($objPHPExcel->getActiveSheet()->getCell("AE" . $row)->getValue());

            if (!empty($index_number) && !empty($fname) && !empty($lname) && !empty($reg_no) && !empty($gender) && !empty($date_of_birth) && !empty($date_of_admission) && !empty($date_of_completion) && !empty($prog_id) && !empty($level) && !empty($cellphone) && !empty($email) && !empty($classification) && !empty($nationality)) {
                $stu = $this->StudentUser->find('first', array(
                    'fields' => array('id','index_number'),
                    'conditions' => array('index_number' => $index_number),
                    'recursive' => -1
                ));
                $dbt_id = 0;
                if ($stu) {
                    $dbt_id = $stu['StudentUser']['id'];
                }
                $pro = $this->Program->find('first', array(
                    'fields' => array('id'),
                    'conditions' => array('progid' => $prog_id),
                    'recursive'=>-1
                ));
                $pid = false;
                if ($pro) {
                    $pid= $pro['Program']['id'];
                }

                if($pid){
                    $save[] = array(
                        'id' => $dbt_id,
                        'username'=>$index_number,
                        'password' => md5($index_number),
                        'index_number' => $index_number,
                        'fname' => ucwords(strtolower($fname)),
                        'lname' => ucwords(strtolower($lname)),
                        'mname' => ucwords(strtolower($mname)),
                        'stud_id' => $stud_id,
                        'reg_no' => $reg_no,
                        'gender' => $gender,
                        'title_id' => $title_id,
                        'date_of_birth' =>  date("Y-m-d", strtotime($date_of_birth)),
                        'date_of_admission' => $date_of_admission,
                        'date_of_completion' => $date_of_completion,
                        'prog_id' => $prog_id,
                        'program_id' => $pid,
                        'major_id' => $major_id,
                        'level' => $level,
                        'entry_level' => $entry_level,
                        'hall_id' => $hall_id,
                        'ssnit' => $ssnit,
                        'nationality' => $nationality,
                        'homephone' =>  $this->clean_fonnumber($homephone),
                        'cellphone' =>  $this->clean_fonnumber($cellphone),
                        'email' => $email,
                        'applicant_id' => $applicant_id,
                        'paytype' => $paytype,
                        'completed' => $completed,
                        'graduate' => $graduate,
                        'status' => $status,
                        'classification' => ucwords(strtolower($classification)),
                        'activestatus' => $activestatus,
                        'src_id' => $src_id,
                        'dn_status' => $dn_status,
                        'created_by' => $auth_user['id']
                    );
                }
                else{
                    return array('status' => false, 'mesg' => 'Program '.$prog_id.' cannot be found for student: '.$index_number);
                }
            }
            else{
                $m = 'Required fields index_number,fname,lname,reg_no,gender,date_of_birth,date_of_admission, <br /> date_of_completion,prog_id,level,cellphone,email,classification,nationality. These fields cannot be empty.';
                return array('status' => false, 'mesg' =>$m );
            }
        }

        # save the data
        if ($this->StudentUser->saveAll($this->StudentUser->sanitize($save))) {
            return array('status' => true, 'mesg' => "The file was successfully imported!");
        } else {
            return array('status' => false, 'mesg' => 'The file could not be imported.');
        }
    }


    function clean_fonnumber($send_no)
    {

        if (!$send_no) {
            return $send_no;
        }
        $cell = '';

        if (strpos($send_no, ',') !== false) {
            $fon_numbers = explode(',', $send_no);
        } elseif (strpos($send_no, '/') !== false) {
            $fon_numbers = explode('/', $send_no);
        } else {
            $fon_numbers = trim($send_no);
        }
        if (is_array($fon_numbers)) {
            $cell = $send_no;
            /*foreach($fon_numbers as $fon){
                $fon = preg_replace('/-/', '', $fon);
                //$fon = preg_replace('/+/', '', $fon);
                $fon = trim($fon);
                if(strlen($fon) < 9){//not a mobile number
                    $cell .=  $fon.'/';
                }
                else{
                    $country_code = substr($fon, 0, 3);
                    $cell .= $fon;
                    if($country_code != '233'){
                        $f= substr($fon_numbers, 0,1);
                        if($f == '0'){
                            $cell .= '233'.substr($fon, 1).'/';
                        }
                        else{
                            $cell .= '233'.$fon.'/';
                        }

                    }
                }


            }*/
        } else {
            $fon_numbers = preg_replace('/-/', '', $fon_numbers);
            //$fon_numbers = preg_replace('/+/', '', $fon_numbers);
            $fon_numbers = trim($fon_numbers);
            if (strlen($fon_numbers) < 9) { //not a mobile number
                return $fon_numbers;
            }
            $country_code = substr($fon_numbers, 0, 3);

            $cell = $fon_numbers;
            if ($country_code != '233') {
                $f = substr($fon_numbers, 0, 1);
                if ($f == '0') {
                    $cell = '233' . substr($fon_numbers, 1);
                } else {
                    $cell = '233' . $fon_numbers;
                }

            }
        }
        //remove the dash separator if any
        return $cell;


    }


    /* School Fee Types list */
    function fee_types($type = 'get')
    {
        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;
            $auth_user = $this->Session->read('loginData');
            switch ($type) {
                case 'get' :

                    $_POST = $this->StaffUser->sanitize($_POST);
                    /**  Get posted data */
                    $page = isset($_POST['page']) ? $_POST['page'] : 1;
                    /** The current page */
                    $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
                    /** Sort column */
                    $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
                    /** Sort order */
                    $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';
                    /** Search string */
                    $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
                    $limit = $rp;
                    $start = ($page - 1) * $rp;

                    //get users id for this company only

                    $condition_array = array(
                        'FeeType.deleted' => 'n',
                        'FeeType.visible' => 'y'
                    );

                    if (!empty($search_query)) {
                        $condition_array = array(
                            "FeeType.$qtype LIKE" => $search_query . '%',
                            'FeeType.deleted' => 'n',
                            'FeeType.visible' => 'y'
                        );
                    }
                    $contain = array(
                        'BankBranch' => array('fields' => array('BankBranch.id', 'BankBranch.branch_name_full')),
                        'Bank' => array('fields' => array('Bank.id', 'Bank.bank_full'))
                    );
                    $data_table = $this->FeeType->find('all', array('conditions' => $condition_array, 'contain' => $contain, 'order' => "FeeType.$sortname $sortorder", 'limit' => $start . ',' . $limit, 'recursive' => 1));
                    $data_table_count = $this->FeeType->find('count', array('conditions' => $condition_array, 'recursive' => -1));

                    $total_records = $data_table_count;

                    if ($data_table) {
                        $return_arr = array();
                        foreach ($data_table as $obj) {
                            $return_arr[] = array(
                                'id' => $obj['FeeType']['id'],
                                'cell' => array(
                                    $obj['FeeType']['id'],
                                    $obj['FeeType']['fee_category'],
                                    ucwords(strtolower($obj['FeeType']['description'])),
                                    $obj['FeeType']['fee_code'],
                                    $obj['FeeType']['account_no'],
                                    $obj['Bank']['bank_full'],
                                    $obj['BankBranch']['branch_name_full'],
                                    $obj['FeeType']['initial_charge'],
                                    $obj['FeeType']['additional_charge'],
                                    $obj['FeeType']['quantity_limit']
                                )
                            );
                        }
                        return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
                    }
                    break;

                case 'save' :
                    $data = array('FeeType' => $_POST);
                    if ($_POST['id'] == 0) {
                        $data['FeeType']['created_by'] = $auth_user['id'];
                    } else {
                        $data['FeeType']['modified_by'] = $auth_user['id'];
                    }

                    $gdata = $this->FeeType->save($data);
                    if ($gdata) {
                        if ($_POST['id'] > 0) {
                            return json_encode(array('code' => 0, 'msg' => 'Data Updated!'));
                        } else {
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved', 'id' => $this->FeeType->id));
                        }
                    } else {
                        return json_encode(array('code' => 1, 'msg' => __('Data cannot be saved')));
                    }

                    break;

                case 'delete':
                    $ids = $_POST['ids'];
                    $modObj = ClassRegistry::init('FeeType');
                    $result = $modObj->updateAll(
                        array('FeeType.deleted' => "'y'", 'FeeType.modified_by' => $auth_user['id']),
                        array('FeeType.id' => $ids)
                    );
                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Deleted!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be deleted'));
                    }
                    break;
            }
        }

        $bank_data = $this->Bank->find('list', array(
            'fields' => array('id', 'bank_full'),
            'recursive' => -1
        ));
        $banks_option = array();
        foreach ($bank_data as $bank_id => $bank_name) {
            $banks_option[] = array(
                'id' => $bank_id,
                'name' => $bank_name
            );
        }
        $bank_branch_data = $this->BankBranch->find('all', array(
            'fields' => array('id', 'bank_id','branch_name_full'),
            'recursive' => -1
        ));
        $branches = array();
        $bank_branches = array();
        foreach ($bank_branch_data as $dt) {
            $new_branch = array(
                'id' => $dt['BankBranch']['id'],
                'name' => $dt['BankBranch']['branch_name_full'],
            );
            $branches[] = $new_branch;
            $bank_branches[ $dt['BankBranch']['bank_id']][$dt['BankBranch']['id']] = $new_branch;
        }

        $quantity_limit_data = array('1' => '1', '2' => '2', '3' => '3', '4' => '4', '5' => '5', 'Any' => 'Any');
        $quantity_limits = array();
        foreach ($quantity_limit_data as $k => $dt) {
            $quantity_limits[] = array(
                'id' => $k,
                'name' => $dt,
            );
        }

        $this->set(compact('branches', 'quantity_limits','banks_option','bank_branches'));
    }


    /* School Fee Types list */
    function academic_sessions($type = 'get')
    {

        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;
            $auth_user = $this->Session->read('loginData');
            switch ($type) {
                case 'get' :
                    $_POST = $this->StaffUser->sanitize($_POST);
                    /**  Get posted data */
                    $page = isset($_POST['page']) ? $_POST['page'] : 1;
                    /** The current page */
                    $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
                    /** Sort column */
                    $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
                    /** Sort order */
                    $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';
                    /** Search string */
                    $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
                    $limit = $rp;
                    $start = ($page - 1) * $rp;

                    //get users id for this company only

                    $condition_array = array( //'AcademicSession.deleted' => 'n',
                    );

                    if (!empty($search_query)) {
                        $condition_array = array(
                            "AcademicSession.$qtype LIKE" => $search_query . '%',
                            //'AcademicSession.deleted' => 'n',
                        );
                    }

                    $data_table = $this->AcademicSession->find('all', array('conditions' => $condition_array, 'order' => "AcademicSession.$sortname $sortorder", 'limit' => $start . ',' . $limit, 'recursive' => -1));
                    $data_table_count = $this->AcademicSession->find('count', array('conditions' => $condition_array, 'recursive' => -1));

                    $total_records = $data_table_count;

                    if ($data_table) {
                        $return_arr = array();
                        foreach ($data_table as $obj) {
                            $def_sess = '';
                            if ($this->current_academic_session_id == $obj['AcademicSession']['id']) {
                                $def_sess = 'Default Academic Session';
                            }
                            $return_arr[] = array(
                                'id' => $obj['AcademicSession']['id'],
                                'cell' => array(
                                    $obj['AcademicSession']['id'],
                                    $obj['AcademicSession']['acc_year'],
                                    $obj['AcademicSession']['semester'],
                                    $obj['AcademicSession']['session'],
                                    $def_sess
                                )
                            );
                        }
                        return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
                    }
                    break;

                case 'save' :

                    $data = array('AcademicSession' => $_POST);
                    $arr = explode('/', $_POST['acc_year']);
                    $semester = trim($_POST['semester']);
                    $val = $semester[0];
                    if ($val != 0) {
                        $semester = '0' . $semester;
                    }
                    $session = $arr[0] . '' . $semester;
                    $data['AcademicSession']['session'] = $session;

                    if ($_POST['id'] == 0) {
                        $data['AcademicSession']['created_by'] = $auth_user['id'];
                    } else {
                        $data['AcademicSession']['modified_by'] = $auth_user['id'];
                    }

                    $gdata = $this->AcademicSession->save($data);
                    if ($gdata) {
                        if ($_POST['id'] > 0) {
                            return json_encode(array('code' => 0, 'msg' => 'Data Updated!'));
                        } else {
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved', 'id' => $this->AcademicSession->id));
                        }
                    } else {
                        return json_encode(array('code' => 1, 'msg' => __('Data cannot be saved')));
                    }

                    break;

                case 'set_default_session':
                    $id = $_POST['id'];
                    $modObj = ClassRegistry::init('Config');
                    $result = $modObj->updateAll(
                        array('Config.academic_session_id' => $id),
                        array('Config.id' => 1)
                    );
                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Updated!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be updated'));
                    }
                    break;
            }
        }

        $this->set(compact(''));
    }


}